package sanrenxing.dayEnd.backRepertoryTotal.dao;

import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import sanrenxing.dayEnd.backRepertoryTotal.domain.SeateMX;

import java.util.List;

/**
 * 功能:
 * 编写者：
 * 日期：2021
 * 版本：1.0
 */
public interface SeateMXDao {


    /**
     * 分页查询条件
     *
     * @param start
     * @param rows
     * @param condition
     * @return
     */

    @Select("select seatId, \n" +
            "nvl((select sum(std.tranNum) from securitiesTradingData std join financialsecurities fl on std.seId=fl.seId where fl.seType=1 and std.may='买入' and st.seatId=std.seatId),0) as buyshares,\n" +
            "nvl((select sum(std.tranNum) from securitiesTradingData std join financialsecurities fl on std.seId=fl.seId where fl.seType=1 and std.may='卖出' and st.seatId=std.seatId),0) as soldshares,\n" +
            "nvl((select sum(std.tranNum) from securitiesTradingData std join financialsecurities fl on std.seId=fl.seId where fl.seType=2 and std.may='买入' and st.seatId=std.seatId),0) as buybond,\n" +
            "nvl((select sum(std.tranNum) from securitiesTradingData std join financialsecurities fl on std.seId=fl.seId where fl.seType=2 and std.may='卖出' and st.seatId=std.seatId),0) as soldbond,\n" +
            "(nvl((select sum(std.tranMoney) from securitiesTradingData std join financialsecurities fl on std.seId=fl.seId where fl.seType=1 and std.may='买入' and st.seatId=std.seatId),0)+\n" +
            "nvl((select sum(std.tranMoney) from securitiesTradingData std join financialsecurities fl on std.seId=fl.seId where fl.seType=1 and std.may='卖出' and st.seatId=std.seatId),0)+\n" +
            "nvl((select sum(std.tranMoney) from securitiesTradingData std join financialsecurities fl on std.seId=fl.seId where fl.seType=2 and std.may='买入' and st.seatId=std.seatId),0)+\n" +
            "nvl((select sum(std.tranMoney) from securitiesTradingData std join financialsecurities fl on std.seId=fl.seId where fl.seType=2 and std.may='卖出' and st.seatId=std.seatId),0)) as totalMoney,to_char(sysdate,'yyyy-MM-dd') as tranDate \n" +
            "from securitiesTradingData st where st.tranStatus=1 group by seatId")
    public List<SeateMX> findByPage(@Param("start") int start, @Param("rows") int rows, @Param("condition") String condition);

    /**
     * 按条件统计记录数
     *
     * @param condition 统计条件
     * @return 条件
     */
    @Select("select count(*) from (select seatId from securitiesTradingData where tranStatus=1 group by seatId) where 1=1 ${condition}")

    public int count(@Param("condition") String condition);
}
